package ku.cat.db;

import java.util.ArrayList;

import ku.cat.db.model.AttendanceModel;
import ku.cat.db.model.CourseModel;
import ku.cat.db.model.InstructorModel;
import ku.cat.db.model.StudentModel;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final int DB_VERSION = 1;
	private static final String DB_NAME = "class-attendant.db";

	// SQL Command for database structure
	// private static final String DB_STRUCTURE_CREATE_ATTENDANCE_LOG_TEXT_V1 =
	// "CREATE TABLE [attendance_log] ([id] INTEGER NOT NULL PRIMARY KEY UNIQUE, [studentrfid] VARCHAR(50), [classid] VARCHAR(8) DEFAULT 'catt', [stamptime] DATETIME NOT NULL, [filename] VARCHAR(60) DEFAULT 'NA', [ready] BOOLEAN DEFAULT 'N', [upload] BOOLEAN DEFAULT 'N');";
	private static final String DB_STRUCTURE_CREATE_ATTENDANCE_LOG_V1 = "CREATE TABLE \"attendance_log\" (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,studentrfid varchar,classid varchar DEFAULT catt,stamptime datetime NOT NULL,filename text DEFAULT NA,ready boolean DEFAULT N,upload boolean DEFAULT N);";
	private static final String DB_STRUCTURE_CREATE_STUDENT_V1 = "CREATE TABLE student (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,rfid varchar NOT NULL UNIQUE,code varchar,firstname text,lastname text);";
	private static final String DB_STRUCTURE_CREATE_TEACHER_V1 = "CREATE TABLE teacher (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,rfid varchar NOT NULL UNIQUE,code varchar,firstname text,lastname text);";
	private static final String DB_STRUCTURE_CREATE_COURSE_V1 = "CREATE TABLE course (\"course_id\" varchar NOT NULL PRIMARY KEY UNIQUE,\"course_name_th\" text,\"course_name_en\" text,credit varchar);";

	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase sqliteDb) {
		sqliteDb.execSQL(DB_STRUCTURE_CREATE_ATTENDANCE_LOG_V1);
		sqliteDb.execSQL(DB_STRUCTURE_CREATE_STUDENT_V1);
		sqliteDb.execSQL(DB_STRUCTURE_CREATE_TEACHER_V1);
		sqliteDb.execSQL(DB_STRUCTURE_CREATE_COURSE_V1);
	}

	@Override
	public void onUpgrade(SQLiteDatabase sqliteDb, int oldVersion,
			int newVersion) {
		// TODO Auto-generated method stub

	}

	/*
	 * method
	 */

	/**
	 * Attendance
	 */

	public long addAttendance(AttendanceModel attendanceModel) {

		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("studentrfid", attendanceModel.getStudentrfid());
		values.put("classid", attendanceModel.getClassid());
		values.put("stamptime", "" + attendanceModel.getStamptime());
		values.put("filename", attendanceModel.getFilename());
		values.put("ready", attendanceModel.isReady());
		values.put("upload", attendanceModel.isUpload());

		long id = db.insert("attendance_log", null, values);

		db.close();

		return id;

	}

	/*
	 * Student
	 */

	public StudentModel[] getStudentAll() {
		SQLiteDatabase db = getWritableDatabase();
		String cols[] = new String[] { "id", "rfid", "code", "firstname",
				"lastname" };
		Cursor cursor = db.query("student", cols, null, null, null, null, null);

		ArrayList<StudentModel> retList = new ArrayList<StudentModel>();
		while (cursor.moveToNext()) {
			StudentModel model = new StudentModel();

			model.setId(cursor.getInt(1));
			model.setRfid(cursor.getString(2));
			model.setCode(cursor.getString(3));
			model.setFirstname(cursor.getString(4));
			model.setLastname(cursor.getString(5));

			retList.add(model);
		}

		StudentModel[] ret = (StudentModel[]) retList.toArray();

		return ret;

	}

	public StudentModel getStudentFromId(String id) {
		SQLiteDatabase db = getWritableDatabase();

		String cols[] = new String[] { "id", "rfid", "code", "firstname",
				"lastname" };

		Cursor cursor = db.query("student", cols, "code=?",
				new String[] { id }, null, null, null);

		if (cursor != null) {
			cursor.moveToFirst();
			StudentModel student = new StudentModel();

			student.setId(cursor.getInt(1));
			student.setRfid(cursor.getString(2));
			student.setCode(cursor.getString(3));
			student.setFirstname(cursor.getString(4));
			student.setLastname(cursor.getString(5));
			return student;
		} else {
			return null;
		}
	}

	public StudentModel getStudentFromRFIDCode(String rfidCode) {
		SQLiteDatabase db = getWritableDatabase();

		String cols[] = new String[] { "id", "rfid", "code", "firstname",
				"lastname" };

		Cursor cursor = db.query("student", cols, "rfid=?",
				new String[] { rfidCode }, null, null, null);

		if (cursor != null) {
			cursor.moveToFirst();
			StudentModel student = new StudentModel();

			student.setId(cursor.getInt(1));
			student.setRfid(cursor.getString(2));
			student.setCode(cursor.getString(3));
			student.setFirstname(cursor.getString(4));
			student.setLastname(cursor.getString(5));
			return student;
		} else {
			return null;
		}
	}

	/*
	 * Instructor
	 */

	public InstructorModel getInstructorFromRFIDCode(String rfidCode) {
		SQLiteDatabase db = getWritableDatabase();

		String cols[] = new String[] { "id", "rfid", "firstname", "lastname" };

		Cursor cursor = db.query("teacher", cols, "rfid=?",
				new String[] { rfidCode }, null, null, null);

		if (cursor != null) {
			cursor.moveToFirst();
			InstructorModel instructor = new InstructorModel();

			instructor.setId(cursor.getInt(1));
			instructor.setRfid(cursor.getString(2));
			instructor.setFirstName(cursor.getString(3));
			instructor.setLastName(cursor.getString(4));

			return instructor;

		} else {
			return null;
		}
	}

	/*
	 * Course
	 */

	public CourseModel[] getCourseAll() {
		SQLiteDatabase db = getWritableDatabase();
		String cols[] = new String[] { "course_id", "course_name_th",
				"course_name_en", "credit" };
		Cursor cursor = db.query("course", cols, null, null, null, null, null);

		ArrayList<CourseModel> retList = new ArrayList<CourseModel>();
		while (cursor.moveToNext()) {
			CourseModel model = new CourseModel();

			model.setCourse_id(cursor.getString(1));
			model.setCourse_name_th(cursor.getString(2));
			model.setCourse_name_en(cursor.getString(3));
			model.setCourse_name_en(cursor.getString(4));
			model.setCredit(cursor.getString(5));

			retList.add(model);
		}

		CourseModel[] ret = (CourseModel[]) retList.toArray();

		return ret;

	}

}
